if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOISummaryXML]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[GetCOISummaryXML]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 
/******************************************************************  
* Name:      GetCOISummaryXML  
* Purpose:     Generates the data required for the summary report in XML  
*  
* PARAMETERS  
* Name         Description       
* -------------     -------------------------------------------  
* @xmlListOfAdmPersonIds Adm Person Ids  
******************************************************************/  
  
CREATE Procedure [dbo].[GetCOISummaryXML] (  
  @xmlListOfAdmPersonIds TEXT,  
  @supervisorName VARCHAR(100),  
  @filterByLatestReportingPeriod bit = false  
)  
AS  
BEGIN  
  
 SET NOCOUNT ON  
   
 declare @admPersonIds table (Sequence int identity, [ID] int, SupervisorId int)   
   
 declare @xmlDoc int          
 exec sp_xml_preparedocument @xmlDoc output, @xmlListOfAdmPersonIds  
 insert into @admPersonIds(id, supervisorId) select [Id], SupervisorId from openxml(@xmlDoc, '//IdList', 2) with (Id int, SupervisorId int)          
 exec sp_xml_removedocument @xmlDoc  
  
 DECLARE @xml XML  
 DECLARE @latestReportingPeriod INT
 SELECT @latestReportingPeriod = MAX(Id) FROM dbo.COIReportingPeriods
  
 SET @xml = (SELECT  @supervisorName as SupervisorName,   
  (SELECT ltrim(rtrim(ADMP.LastName)) + ', ' + ltrim(rtrim(ADMP.FirstName)) as [Name],   
    ltrim(rtrim(SADMP.LastName)) + ', ' + ltrim(rtrim(SADMP.FirstName)) as [ImmdSupervisorName],  
    --ADMP.Title,   
    IsNull(ADMP.OutsideMember, 0) as OutsideMember,    
    case COI.HasOutsideActivities when 1 then 'Y' when 0 then 'N' else '' end as HasOutsideActivities,   
    case COI.HasEquityInterests when 1 then 'Y' when 0 then 'N' else '' end as HasEquityInterests,   
    case COI.HasFamilyAssociations when 1 then 'Y' when 0 then 'N' else '' end as HasFamilyAssociations,   
	case COI.Royalty when 1 then 'Y' when 0 then 'N' else '' end as HasRoyalties,   
    case COI.PolicyViolation when 1 then 'Y' when 0 then 'N' else '' end as PolicyViolation,   
    case COI.OtherActivity when 1 then 'Y' when 0 then 'N' else '' end as OtherActivity,  
    convert(varchar, DateSubmitted, 101) as DateSubmitted,  
    COI.Status as Status  
  FROM @admPersonIds Ids  
   INNER JOIN dbo.AdmPerson ADMP ON Ids.Id = ADMP.Id  
   INNER JOIN dbo.AdmPerson SADMP ON Ids.SupervisorId = SADMP.Id  
   LEFT JOIN (  
      SELECT *   
      FROM dbo.conflictofinterest  
      WHERE Id in (  
         SELECT Max(COI.Id)   
         FROM dbo.ConflictOfInterest COI  
          INNER JOIN @admPersonIds AP ON COI.AdmPersonId = AP.Id  
         WHERE COI.Status IN ('COISTATUS_SUBMITTED', 'COISTATUS_REVIEWED') 
         AND (@filterByLatestReportingPeriod = 0 OR COI.ReportingPeriodId = @latestReportingPeriod)
         GROUP BY AP.Id)  
     ) COI ON ADMP.Id = COI.AdmPersonId  
  ORDER BY SADMP.LastName, SADMP.FirstName, ADMP.LastName, ADMP.FirstName  
  FOR XML PATH('COISummaryReport'),TYPE)  
 FOR XML RAW('COISummaryReports'))  
  
 SELECT @xml  
   
END  

